﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//................
using QLKTX1._0.DTO;
using QLKTX1._0.DB;
using System.Data.SqlClient;
using System.Data;
//.................

namespace QLKTX1._0.DAO
{
    class DotDangKyMoiDAO
    {

        public static DotDangKyDTO getObject(string maDot)
        {
            ConnectDB con = new ConnectDB();

            DotDangKyDTO dotDangKy = new DotDangKyDTO();

            string sql = "";

            sql += "SELECT dbo.Dot_DangKy.maDot, dbo.Dot_DangKy.ngayBatDau,dbo.Dot_DangKy.tinhTrang, dbo.Dot_DangKy.ngayKetThuc, dbo.Dot_DangKy.tenDot, dbo.namHoc.maNamHoc,";
            sql += " dbo.namHoc.tenNamHoc FROM dbo.Dot_DangKy INNER JOIN";
            sql += " dbo.namHoc ON dbo.Dot_DangKy.maNamHoc = dbo.namHoc.maNamHoc";
            sql += " WHERE dbo.Dot_DangKy.maDot='" + maDot + "'";

            DataTable dt = con.getDataTable(sql);

            for (int i = 0; i < dt.Rows.Count; i++)
            {


                dotDangKy.MaDot = dt.Rows[i]["maDot"].ToString();

                dotDangKy.NgayBatDau = DateTime.Parse(dt.Rows[i]["ngayBatDau"].ToString());

                dotDangKy.NgayKetThuc = DateTime.Parse(dt.Rows[i]["ngayKetThuc"].ToString());

                dotDangKy.TenDot = dt.Rows[i]["tenDot"].ToString();

                NamHocDTO nh = new NamHocDTO();

                nh.MaNamHoc = Int32.Parse(dt.Rows[i]["maNamHoc"].ToString());

                nh.TenNamHoc = dt.Rows[i]["tenNamHoc"].ToString();

                dotDangKy.MaNamHoc = nh;

                dotDangKy.TinhTrang = Int32.Parse(dt.Rows[i]["tinhTrang"].ToString());

                //-------------------------//    
            }
            return dotDangKy;
        }

        public static List<DotDangKyDTO> dsDotDangKy()
        {
            List<DotDangKyDTO >dsDot = new List<DotDangKyDTO> ();


            ConnectDB con = new ConnectDB();
            string sql = " SELECT dbo.Dot_DangKy.maDot, dbo.Dot_DangKy.ngayBatDau, dbo.Dot_DangKy.ngayKetThuc, dbo.Dot_DangKy.tenDot, dbo.Dot_DangKy.tinhTrang,dbo.namHoc.maNamHoc, dbo.namHoc.tenNamHoc FROM dbo.Dot_DangKy INNER JOIN dbo.namHoc ON dbo.Dot_DangKy.maNamHoc = dbo.namHoc.maNamHoc";

            DataTable dt = con.getDataTable(sql);


            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DotDangKyDTO dotDangKy = new DotDangKyDTO();

                dotDangKy.MaDot = dt.Rows[i]["maDot"].ToString();

                dotDangKy.NgayBatDau = Convert.ToDateTime(dt.Rows[i]["ngayBatDau"].ToString());

                dotDangKy.NgayKetThuc = Convert.ToDateTime(dt.Rows[i]["ngayKetThuc"].ToString());

                dotDangKy.TenDot = dt.Rows[i]["tenDot"].ToString();

                dotDangKy.TinhTrang = Int32.Parse(dt.Rows[i]["tinhTrang"].ToString());

                NamHocDTO nh = new NamHocDTO();
                nh.MaNamHoc = Int32.Parse(dt.Rows[i]["maNamHoc"].ToString());
                nh.TenNamHoc = dt.Rows[i]["tenNamHoc"].ToString();
                dotDangKy.MaNamHoc = nh;

                //-------------------------//             

                dsDot.Add(dotDangKy);
            }

            return dsDot;
        }
        //Ham Insert
        public static bool themDotDangKy(DotDangKyDTO ddk)
        {
            ConnectDB con = new ConnectDB();// dung DB

            return con.executeQuery("INSERT INTO Dot_DangKy(maDot,ngayBatDau,ngayKetThuc,tenDot,tinhTrang,maNamHoc) VALUES('"+ddk .MaDot +"','" + ddk.NgayBatDau.ToString("yyyy-MM-dd") + "','" + ddk.NgayKetThuc.ToString("yyyy-MM-dd") + "',N'"+ ddk.TenDot +"',"+ddk .TinhTrang +","+ddk .MaNamHoc .MaNamHoc +")");

        }
        public static bool xoaDotDangKy(DotDangKyDTO ddk)
        {
            ConnectDB con = new ConnectDB();
            return con.executeQuery("DELETE FROM Dot_DangKy WHERE maDot='" + ddk .MaDot + "' ");
        }
        public static bool capNhatDotDangKy(DotDangKyDTO ddk)
        {
            ConnectDB con = new ConnectDB();
            return con.executeQuery(" UPDATE Dot_DangKy set ngayBatDau='" + ddk.NgayBatDau.ToString("yyyy-MM-dd") + "',ngayKetThuc='" + ddk.NgayKetThuc.ToString("yyyy-MM-dd") + "',tenDot=N'" + ddk.TenDot + "',tinhTrang=" + ddk.TinhTrang + ",maNamHoc= "+ddk .MaNamHoc .MaNamHoc +" WHERE maDot='"+ddk .MaDot +"'");
        }
   
    }
}
